﻿USE [ReTS_Db]
GO 

PRINT ''
PRINT 'Creating stored procedures for Project table...'

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SelectProjectById]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_SelectProjectById]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_SelectProjectById]
    @Project_Id int
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM [Project] WHERE Project_Id = @Project_Id

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_InsertProject]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_InsertProject]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_InsertProject]
    @Title nvarchar(50),
	@Description nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [Project] 
	(
	Title,
	Description
	)
VALUES 
	(
	@Title,
	@Description
	)

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_UpdateProject]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_UpdateProject]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_UpdateProject]
    @Project_Id int,
    @Title nvarchar(50),
    @Description nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [Project] 
SET 
	Title = @Title,
	Description = @Description
WHERE Project_Id = @Project_Id

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DeleteProject]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_DeleteProject]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_DeleteProject]
    @Project_Id int
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM [Project] 
WHERE Project_Id = @Project_Id

END
'
GO 